create schema test_ustore_vacuum_full;
set current_schema = test_ustore_vacuum_full;
-- test normal table
drop table if exists testt1;
create table testt1 (c1 int, c2 text default 'abcd') with (storage_type=USTORE);
create index t1i1 on testt1(c1);
begin;
insert into testt1 (c1, c2) values (1, DEFAULT);
insert into testt1 (c1, c2) values (2, 'test2test');
insert into testt1 (c1, c2) values (3, 'test3test');
insert into testt1 (c1, c2) values (4, 'test4test');
insert into testt1 (c1, c2) values (5, 'test5test');
insert into testt1 (c1, c2) values (6, 'test6test');
insert into testt1 (c1, c2) values (7, 'test7test');
update testt1 set c1 = c1 + 1;
commit;
delete from testt1 where c1 < 6;
vacuum full testt1;
select * from testt1;
drop table testt1;

-- test partition table
drop table if exists partition_t2;
create table partition_t2
(
c1 int,
c2 int,
c3 text default 'test_partition'
) with (storage_type=USTORE)
partition by range(c1)
(
partition p1 values less than(100),
partition p2 values less than(200)
);
create index local_pt2i on partition_t2(c1) local;
create index global_pt2i on partition_t2(c2) global;
insert into partition_t2 (c1, c2, c3) values (generate_series(1, 50), DEFAULT, DEFAULT);
insert into partition_t2 (c1, c2, c3) values (generate_series(100, 150), DEFAULT, DEFAULT);
delete from partition_t2 where c1 < 48;
delete from partition_t2 where c1 > 102;
vacuum full partition_t2;
select * from partition_t2;
drop table partition_t2;

-- test toast
drop table if exists toasttest;
create table toasttest (col1 int4, col2 int4, col3 text default 'testing', col4 text, b integer generated always as (length(col3)) stored) with (storage_type=USTORE);
insert into toasttest values(40, 60, repeat('y', 1000000), 'ZZZZZZZZZZ');
begin;
insert into toasttest values(generate_series(0, 9), 60, repeat('y', 300000), 'ZZZZZZZZZZ');
insert into toasttest values(generate_series(10, 20), 60, repeat('x', 100), 'XXXXX');
commit;
create index idx1 on toasttest(col2);
delete from toasttest where col1 < 18;
vacuum full toasttest;
select col1, col2, length(col3), col4, b from toasttest;
drop table toasttest;

-- test temp table 
create temporary table temptest (c1 int, c2 text default 'test') on commit preserve rows;
insert into temptest values(generate_series(1, 100), default);
delete from temptest where c1 > 5;
vacuum full temptest;
select * from temptest;
drop table temptest;

-- test alter
drop table if exists partition_t2;
create table partition_t2
(
c1 int,
c2 int,
c3 text default 'test_partition'
) with (storage_type=USTORE)
partition by range(c1)
(
partition p1 values less than(100),
partition p2 values less than(200)
);
create index local_pt2i on partition_t2(c1) local;
create index global_pt2i on partition_t2(c2) global;
insert into partition_t2 (c1, c2, c3) values (generate_series(1, 150), default, default);
-- split
alter table partition_t2 split partition p1 at(50) into(partition p1_1, partition p1_2);
delete from partition_t2 where c1 < 49;
delete from partition_t2 where c1 < 99 and c1 > 50;
delete from partition_t2 where c1 > 100 and c1 < 150;
\di+ global_pt2i
vacuum full partition_t2;
\di+ global_pt2i
select * from partition_t2;
-- merge 
truncate table partition_t2;
insert into partition_t2 values (generate_series(1, 150), default, default);
create index global_pt2i on partition_t2(c2) global;
alter table partition_t2 merge partitions p1_1,p1_2 into partition p1 update global index;
delete from partition_t2 where c1 > 2;
vacuum full partition_t2;
\di+ global_pt2i
select * from partition_t2;
-- exchange
truncate table partition_t2;
insert into partition_t2 values(1, default, default);
drop table if exists testt1;
create table testt1 (c1 int, c2 int, c3 text default 'test_partition') with (storage_type=USTORE);
insert into testt1 (c1, c2, c3) values (generate_series(1, 50), default, default);
create index on testt1(c1);
alter table partition_t2 exchange partition (p1) with table testt1 update global index;
select count(*) from partition_t2;
select * from testt1;
delete from partition_t2 where c1 > 2;
vacuum full partition_t2;
select * from partition_t2;
select * from testt1;
drop table testt1;
-- move
truncate table partition_t2;
insert into partition_t2 (c1, c2, c3) values (generate_series(1, 150), default, default);
create tablespace example1 relative location 'tablespace1/tablespace_1';
alter table partition_t2 move partition p2 tablespace example1;
delete from partition_t2 where c1 < 148;
vacuum full partition_t2;
select * from partition_t2;
alter table partition_t2 move partition p2 tablespace pg_default;
drop tablespace example1;
-- drop
truncate table partition_t2;
insert into partition_t2 (c1, c2, c3) values (generate_series(1, 150), default, default);
alter table partition_t2 drop partition p1 update global index;
vacuum full partition_t2;
select count(*) from partition_t2;
drop table partition_t2;

reset search_path;
drop schema test_ustore_vacuum_full cascade;